package com.pengy.shengxian.service;

import com.pengy.shengxian.entity.CgdDetail;
import com.pengy.shengxian.entity.CgdInfo;
import com.pengy.shengxian.entity.PsdDetail;
import com.pengy.shengxian.entity.PsdInfo;
import com.pengy.shengxian.mapper.CgdDetailMapper;
import com.pengy.shengxian.mapper.CgdInfoMapper;
import com.pengy.shengxian.mapper.GoodsInfoMapper;
import com.pengy.shengxian.mapper.PsdDetailMapper;
import com.pengy.shengxian.mapper.PsdInfoMapper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

@Service
public class DeliveryOrderService {

    private static final Logger logger = LoggerFactory.getLogger(DeliveryOrderService.class);

    @Autowired
    private PsdInfoMapper psdInfoMapper;

    @Autowired
    private PsdDetailMapper psdDetailMapper;

    @Autowired
    private CgdInfoMapper cgdInfoMapper;

    @Autowired
    private CgdDetailMapper cgdDetailMapper;

    @Autowired
    private GoodsInfoMapper goodsInfoMapper;

    // 商品用途编码与名称对应关系（以编码为key，名称为value）
    private static final Map<String, String> PURPOSE_CODE_TO_NAME_MAP = new HashMap<>();
    static {
        PURPOSE_CODE_TO_NAME_MAP.put("30226", "员工餐");
        PURPOSE_CODE_TO_NAME_MAP.put("30208", "早餐");
        PURPOSE_CODE_TO_NAME_MAP.put("30217", "包厢");
        PURPOSE_CODE_TO_NAME_MAP.put("30720", "风味");
        PURPOSE_CODE_TO_NAME_MAP.put("30887", "调味品");
        PURPOSE_CODE_TO_NAME_MAP.put("30762", "咖啡吧");
        PURPOSE_CODE_TO_NAME_MAP.put("30259", "卤菜");
        PURPOSE_CODE_TO_NAME_MAP.put("60602", "彩食鲜咖啡吧");
        PURPOSE_CODE_TO_NAME_MAP.put("60603", "彩食鲜干货");
        PURPOSE_CODE_TO_NAME_MAP.put("60604", "大厅");
        PURPOSE_CODE_TO_NAME_MAP.put("60605", "后厨");
        PURPOSE_CODE_TO_NAME_MAP.put("60606", "包厢物料");
        PURPOSE_CODE_TO_NAME_MAP.put("60607", "包厢1");
        PURPOSE_CODE_TO_NAME_MAP.put("60608", "包厢2");
        PURPOSE_CODE_TO_NAME_MAP.put("30568", "外卖");
        PURPOSE_CODE_TO_NAME_MAP.put("31288", "麻辣烫");
        PURPOSE_CODE_TO_NAME_MAP.put("60609", "减脂餐");
    }
    // 反向映射：名称->编码
    private static final Map<String, String> PURPOSE_NAME_TO_CODE_MAP = new HashMap<>();
    static {
        for (Map.Entry<String, String> entry : PURPOSE_CODE_TO_NAME_MAP.entrySet()) {
            PURPOSE_NAME_TO_CODE_MAP.put(entry.getValue(), entry.getKey());
        }
    }

    /**
     * 配送单处理结果类
     */
    public static class DeliveryOrderProcessResult {
        private List<PsdInfo> processedData;
        private List<PsdDetail> processedDetailData;
        private int totalCount;
        private int successCount;
        private int errorCount;
        private int detailCount;
        private String errorMessage;

        public DeliveryOrderProcessResult() {
            this.processedData = new ArrayList<>();
            this.processedDetailData = new ArrayList<>();
            this.totalCount = 0;
            this.successCount = 0;
            this.errorCount = 0;
            this.detailCount = 0;
        }

        // Getters and Setters
        public List<PsdInfo> getProcessedData() {
            return processedData;
        }

        public void setProcessedData(List<PsdInfo> processedData) {
            this.processedData = processedData;
        }

        public List<PsdDetail> getProcessedDetailData() {
            return processedDetailData;
        }

        public void setProcessedDetailData(List<PsdDetail> processedDetailData) {
            this.processedDetailData = processedDetailData;
        }

        public int getTotalCount() {
            return totalCount;
        }

        public void setTotalCount(int totalCount) {
            this.totalCount = totalCount;
        }

        public int getSuccessCount() {
            return successCount;
        }

        public void setSuccessCount(int successCount) {
            this.successCount = successCount;
        }

        public int getErrorCount() {
            return errorCount;
        }

        public void setErrorCount(int errorCount) {
            this.errorCount = errorCount;
        }

        public int getDetailCount() {
            return detailCount;
        }

        public void setDetailCount(int detailCount) {
            this.detailCount = detailCount;
        }

        public String getErrorMessage() {
            return errorMessage;
        }

        public void setErrorMessage(String errorMessage) {
            this.errorMessage = errorMessage;
        }

        /**
         * 获取文本化处理结果
         *
         * @param fileName 文件名
         * @return 文本结果
         */
        public String getTextResult(String fileName) {
            if (errorCount > 0 || errorMessage != null) {
                return String.format("%s处理失败，原因：%s", fileName, errorMessage != null ? errorMessage : "未知错误");
            } else {
                String psdCode = processedData != null && !processedData.isEmpty() ? processedData.get(0).getPsdHjkCode() : "";
                return String.format("%s处理成功，单号：%s，导入%d条商品明细;", fileName, psdCode, detailCount);
            }
        }
    }

    /**
     * 采购单处理结果类
     */
    public static class ProcurementOrderProcessResult {
        private List<CgdInfo> processedData;
        private List<CgdDetail> processedDetailData;
        private int totalCount;
        private int successCount;
        private int errorCount;
        private int detailCount;
        private String errorMessage;

        public ProcurementOrderProcessResult() {
            this.processedData = new ArrayList<>();
            this.processedDetailData = new ArrayList<>();
            this.totalCount = 0;
            this.successCount = 0;
            this.errorCount = 0;
            this.detailCount = 0;
        }

        // Getters and Setters
        public List<CgdInfo> getProcessedData() {
            return processedData;
        }

        public void setProcessedData(List<CgdInfo> processedData) {
            this.processedData = processedData;
        }

        public List<CgdDetail> getProcessedDetailData() {
            return processedDetailData;
        }

        public void setProcessedDetailData(List<CgdDetail> processedDetailData) {
            this.processedDetailData = processedDetailData;
        }

        public int getTotalCount() {
            return totalCount;
        }

        public void setTotalCount(int totalCount) {
            this.totalCount = totalCount;
        }

        public int getSuccessCount() {
            return successCount;
        }

        public void setSuccessCount(int successCount) {
            this.successCount = successCount;
        }

        public int getErrorCount() {
            return errorCount;
        }

        public void setErrorCount(int errorCount) {
            this.errorCount = errorCount;
        }

        public int getDetailCount() {
            return detailCount;
        }

        public void setDetailCount(int detailCount) {
            this.detailCount = detailCount;
        }

        public String getErrorMessage() {
            return errorMessage;
        }

        public void setErrorMessage(String errorMessage) {
            this.errorMessage = errorMessage;
        }
    }

    /**
     * 将列索引转换为列字母
     */
    private String getColumnLetter(int columnIndex) {
        if (columnIndex < 0) {
            return "";
        }

        StringBuilder result = new StringBuilder();
        while (columnIndex >= 0) {
            result.insert(0, (char) ('A' + columnIndex % 26));
            columnIndex = columnIndex / 26 - 1;
        }
        return result.toString();
    }

    /**
     * 获取单元格值，处理合并单元格的情况（用于采购单处理）
     */
    private String getCellValueWithMergedRegions(Sheet sheet, int rowIndex, int colIndex) {
        // 首先检查当前单元格是否在合并区域内
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(rowIndex, colIndex)) {
                // 如果在合并区域内，取合并区域左上角单元格的值
                Row firstRow = sheet.getRow(range.getFirstRow());
                if (firstRow != null) {
                    Cell firstCell = firstRow.getCell(range.getFirstColumn());
                    if (firstCell != null) {
                        String value = getCellValueAsString(firstCell);
                        return value;
                    }
                }
            }
        }

        // 如果不在合并区域内，直接读取当前单元格
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            Cell cell = row.getCell(colIndex);
            String value = getCellValueAsString(cell);
            return value;
        }

        return null;
    }

    /**
     * 获取单元格的字符串值（用于采购单处理）
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }



    /**
     * 读取配送单HTML文件（支持Microsoft Office HTML格式）
     */
    public List<Map<String, Object>> readDeliveryOrderExcel(File file) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();

        try {
            // 读取HTML文件内容
            String htmlContent = readFileContent(file);
            logger.info("开始处理HTML格式文件: {}，文件大小: {} 字节", file.getName(), htmlContent.length());

            // 解析HTML表格数据
            data = parseHtmlTableData(htmlContent);

            logger.info("从HTML文件中解析到 {} 行数据", data.size());

        } catch (Exception e) {
            logger.error("读取HTML文件时发生错误: {}", file.getName(), e);
            throw new IOException("无法读取HTML文件: " + e.getMessage(), e);
        }

        return data;
    }

    /**
     * 读取文件内容
     */
    private String readFileContent(File file) throws IOException {
        StringBuilder content = new StringBuilder();
        try (java.io.BufferedReader reader = new java.io.BufferedReader(
                new java.io.InputStreamReader(new java.io.FileInputStream(file), "UTF-8"))) {
            String line;
            while ((line = reader.readLine()) != null) {
                content.append(line).append("\n");
            }
        }
        return content.toString();
    }

    /**
     * 解析HTML表格数据
     */
    private List<Map<String, Object>> parseHtmlTableData(String htmlContent) {
        List<Map<String, Object>> data = new ArrayList<>();

        try {
            // 提取表格行数据
            java.util.regex.Pattern rowPattern = java.util.regex.Pattern.compile(
                    "<tr[^>]*>(.*?)</tr>", java.util.regex.Pattern.DOTALL);
            java.util.regex.Matcher rowMatcher = rowPattern.matcher(htmlContent);

            int rowIndex = 0;
            while (rowMatcher.find()) {
                String rowHtml = rowMatcher.group(1);
                Map<String, Object> rowData = parseTableRow(rowHtml, rowIndex);

                if (!rowData.isEmpty()) {
                    data.add(rowData);
                    logger.debug("解析第{}行数据: {}", rowIndex + 1, rowData);
                }
                rowIndex++;
            }

        } catch (Exception e) {
            logger.error("解析HTML表格数据时发生错误", e);
            throw new RuntimeException("解析HTML表格数据失败: " + e.getMessage(), e);
        }

        return data;
    }

    /**
     * 解析表格行数据
     */
    private Map<String, Object> parseTableRow(String rowHtml, int rowIndex) {
        Map<String, Object> rowData = new HashMap<>();

        try {
            // 提取单元格数据
            java.util.regex.Pattern cellPattern = java.util.regex.Pattern.compile(
                    "<td[^>]*colspan=\"(\\d+)\"[^>]*>(.*?)</td>|<td[^>]*>(.*?)</td>",
                    java.util.regex.Pattern.DOTALL);
            java.util.regex.Matcher cellMatcher = cellPattern.matcher(rowHtml);

            int colIndex = 0;
            while (cellMatcher.find()) {
                String cellContent = cellMatcher.group(2) != null ? cellMatcher.group(2) : cellMatcher.group(3);
                String cleanContent = cleanHtmlContent(cellContent);

                if (cleanContent != null && !cleanContent.trim().isEmpty()) {
                    String columnKey = getColumnLetter(colIndex);
                    rowData.put(columnKey, cleanContent.trim());
                    logger.debug("第{}行第{}列({}): {}", rowIndex + 1, colIndex + 1, columnKey, cleanContent.trim());
                }

                colIndex++;
            }

        } catch (Exception e) {
            logger.error("解析表格行数据时发生错误，行号: {}", rowIndex + 1, e);
        }

        return rowData;
    }

    /**
     * 清理HTML内容，提取纯文本
     */
    private String cleanHtmlContent(String htmlContent) {
        if (htmlContent == null) {
            return null;
        }

        // 移除HTML标签
        String cleanContent = htmlContent.replaceAll("<[^>]*>", "");

        // 移除HTML实体
        cleanContent = cleanContent.replaceAll("&nbsp;", " ");
        cleanContent = cleanContent.replaceAll("&amp;", "&");
        cleanContent = cleanContent.replaceAll("&lt;", "<");
        cleanContent = cleanContent.replaceAll("&gt;", ">");
        cleanContent = cleanContent.replaceAll("&quot;", "\"");

        // 移除多余的空白字符
        cleanContent = cleanContent.replaceAll("\\s+", " ").trim();

        return cleanContent;
    }

    /**
     * 从Map中获取字符串值
     */
    public String getStringValue(Map<String, Object> rowData, String columnKey) {
        Object value = rowData.get(columnKey);
        return value != null ? value.toString().trim() : null;
    }

    /**
     * 从Excel数据中提取配送单主表信息（按照新需求重构，支持HTML格式）
     * 返回值增加一个字段：用途编码 purposeCode
     */
    private static class PsdInfoWithPurpose {
        public PsdInfo psdInfo;
        public String purposeCode;
    }

    /**
     * 工具方法：将日期归零（只保留年月日，时分秒清零）
     */
    public static Date toZeroTime(Date date) {
        if (date == null) return null;
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        return cal.getTime();
    }

    // 从Excel数据中提取主表对象（含日期归零）
    private PsdInfoWithPurpose extractPsdInfoWithPurposeFromExcelData(List<Map<String, Object>> excelData, String psdDept) {
        PsdInfoWithPurpose infoWithPurpose = new PsdInfoWithPurpose();
        if (excelData.size() < 2) {
            throw new IllegalArgumentException("HTML数据行数不足，需要至少2行数据，当前只有" + excelData.size() + "行");
        }
        try {
            logger.info("开始提取配送单信息，数据行数: {}", excelData.size());
            for (int i = 0; i < Math.min(3, excelData.size()); i++) {
                logger.debug("第{}行数据: {}", i + 1, excelData.get(i));
            }
            PsdInfo psdInfo = new PsdInfo();
            String orderNumber = null;
            String customerName = null;
            String deliveryDate = null;
            String purposeCode = null;
            // 解析第二行第一个单元格
            Map<String, Object> row2 = excelData.get(1);
            String cellA2 = getStringValue(row2, "A");
            // 新增：直接从A2提取客户名称
            String dept = "";
            if (cellA2 != null && cellA2.contains("客户名称：")) {
                int idx = cellA2.indexOf("客户名称：");
                dept = cellA2.substring(idx + 5).trim();
                logger.info("直接从第二行A列提取部门: {}", dept);
            }
            if (cellA2 != null && cellA2.contains("（") && cellA2.contains("）")) {
                int start = cellA2.indexOf("（");
                int end = cellA2.indexOf("）");
                if (start != -1 && end != -1 && end > start) {
                    String purposeName = cellA2.substring(start + 1, end).trim();
                    purposeCode = PURPOSE_NAME_TO_CODE_MAP.get(purposeName);
                    logger.info("提取用途名称:{}，映射编码:{}", purposeName, purposeCode);
                }
            }
            // 其余主表字段提取逻辑保持不变
            for (Map<String, Object> rowData : excelData) {
                for (Map.Entry<String, Object> entry : rowData.entrySet()) {
                    String value = entry.getValue() != null ? entry.getValue().toString() : "";
                    if (orderNumber == null && value.contains("订单号：")) {
                        orderNumber = extractOrderNumber(value);
                        logger.info("找到订单号: {}", orderNumber);
                    }
                    if (customerName == null && value.contains("客户名称：")) {
                        customerName = value;
                        logger.info("找到客户名称: {}", customerName);
                    }
                    if (deliveryDate == null && value.contains("配送时间：")) {
                        deliveryDate = extractDeliveryDate(value);
                        logger.info("找到配送时间: {}", deliveryDate);
                    }
                }
            }
            if (orderNumber == null || orderNumber.trim().isEmpty()) {
                throw new IllegalArgumentException("HTML中未找到订单号，请检查文件格式");
            }
            psdInfo.setPsdHjkCode(orderNumber.trim());
            psdInfo.setPsdInfoSupplier("AHYJSX");
            logger.info("设置供应商编码: {}", psdInfo.getPsdInfoSupplier());
            psdInfo.setPsdInfoCustomerName("");
            logger.info("设置客户名称为空");
            // 只用A2的客户名称赋值psdDept
            psdInfo.setPsdDept(dept);
            logger.info("最终部门字段: {}", dept);
            if (deliveryDate == null || deliveryDate.trim().isEmpty()) {
                throw new IllegalArgumentException("HTML中未找到配送时间，请检查文件格式");
            }
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date deliveryDateObj = sdf.parse(deliveryDate.trim());
                deliveryDateObj = toZeroTime(deliveryDateObj);
                psdInfo.setPsdInfoDate(deliveryDateObj);
                logger.info("提取配送日期: {}", deliveryDateObj);
            } catch (ParseException e) {
                throw new IllegalArgumentException("配送时间格式错误，请使用yyyy-MM-dd格式: " + deliveryDate);
            }
            // 根据配送部门设置配送员和收货人信息
            if ("T002".equals(psdDept)) {
                psdInfo.setPsdInfoPsyName("汪自刚");
                psdInfo.setPsdInfoPsyPhone("18105690545");
                psdInfo.setPsdInfoReceiverAddress("安徽省合肥市政务区祁门路1669号联通大厦4楼");
                psdInfo.setPsdInfoReceiverName("张志伟");
                psdInfo.setPsdInfoReceiverPhone("18655053472");
            } else if ("T003".equals(psdDept)) {
                psdInfo.setPsdInfoPsyName("杨磊");
                psdInfo.setPsdInfoPsyPhone("18654131179");
                psdInfo.setPsdInfoReceiverAddress("包河区马鞍山路68号中国联合网络通信有限公司合肥市分公司");
                psdInfo.setPsdInfoReceiverName("李英华");
                psdInfo.setPsdInfoReceiverPhone("15655150176");
            } else {
                // 默认值
                psdInfo.setPsdInfoReceiverName("张志伟");
                psdInfo.setPsdInfoReceiverPhone("18655053472");
                psdInfo.setPsdInfoReceiverAddress("安徽省合肥市政务区祁门路1669号联通大厦4楼");
            }
            psdInfo.setPsdInfoSyncStatus(0);
            logger.info("设置收货人姓名: {}", psdInfo.getPsdInfoReceiverName());
            logger.info("设置收货人电话: {}", psdInfo.getPsdInfoReceiverPhone());
            logger.info("配送单信息提取完成: {}", psdInfo);
            PsdInfoWithPurpose result = new PsdInfoWithPurpose();
            result.psdInfo = psdInfo;
            result.purposeCode = purposeCode;
            return result;
        } catch (Exception e) {
            logger.error("提取配送单信息时发生错误", e);
            if (e instanceof IllegalArgumentException) {
                throw e;
            } else {
                throw new RuntimeException("提取配送单信息失败: " + e.getMessage(), e);
            }
        }
    }

    /**
     * 从订单号单元格中提取订单号
     * 格式：订单号：DD250706102344741136
     */
    private String extractOrderNumber(String orderNumberCell) {
        if (orderNumberCell == null || orderNumberCell.trim().isEmpty()) {
            return null;
        }

        String cellValue = orderNumberCell.trim();
        int colonIndex = cellValue.indexOf('：');
        if (colonIndex != -1 && colonIndex < cellValue.length() - 1) {
            return cellValue.substring(colonIndex + 1).trim();
        }

        // 尝试英文冒号
        colonIndex = cellValue.indexOf(':');
        if (colonIndex != -1 && colonIndex < cellValue.length() - 1) {
            return cellValue.substring(colonIndex + 1).trim();
        }

        return null;
    }

    /**
     * 从配送时间单元格中提取日期
     * 格式：配送时间：2025-07-07
     */
    private String extractDeliveryDate(String deliveryDateCell) {
        if (deliveryDateCell == null || deliveryDateCell.trim().isEmpty()) {
            return null;
        }

        String cellValue = deliveryDateCell.trim();
        int colonIndex = cellValue.indexOf('：');
        if (colonIndex != -1 && colonIndex < cellValue.length() - 1) {
            return cellValue.substring(colonIndex + 1).trim();
        }

        // 尝试英文冒号
        colonIndex = cellValue.indexOf(':');
        if (colonIndex != -1 && colonIndex < cellValue.length() - 1) {
            return cellValue.substring(colonIndex + 1).trim();
        }

        return null;
    }


    /**
     * 从客户名称中提取用途名称（中文括号中的内容）
     *
     * @param customerName 客户名称，格式如：中国联合网络通信有限公司安徽省分公司（风味）
     * @return 用途名称，如：风味
     */
    private String extractPurposeFromCustomerName(String customerName) {
        if (customerName == null || customerName.trim().isEmpty()) {
            return null;
        }

        String name = customerName.trim();

        // 查找中文括号的位置
        int startIndex = name.indexOf("（");
        int endIndex = name.indexOf("）");

        if (startIndex != -1 && endIndex != -1 && endIndex > startIndex) {
            String purpose = name.substring(startIndex + 1, endIndex);
            logger.debug("从客户名称中提取用途: {} -> {}", customerName, purpose);
            return purpose;
        }

        // 尝试英文括号
        startIndex = name.indexOf("(");
        endIndex = name.indexOf(")");

        if (startIndex != -1 && endIndex != -1 && endIndex > startIndex) {
            String purpose = name.substring(startIndex + 1, endIndex);
            logger.debug("从客户名称中提取用途: {} -> {}", customerName, purpose);
            return purpose;
        }

        logger.warn("无法从客户名称中提取用途: {}", customerName);
        return null;
    }

    /**
     * 获取友好的异常提示信息
     */
    private String getFriendlyErrorMessage(Exception e) {
        String message = e.getMessage();
        if (message == null) {
            message = e.getClass().getSimpleName();
        }

        // 限制消息长度，避免界面显示不全
        if (message.length() > 100) {
            message = message.substring(0, 97) + "...";
        }

        // 根据异常类型提供更友好的提示
        if (e instanceof IOException) {
            return "文件读取失败，请检查文件格式是否正确";
        } else if (e instanceof ParseException) {
            return "日期格式错误，请检查Excel中的日期格式";
        } else if (e instanceof NumberFormatException) {
            return "数据格式错误，请检查Excel中的数字格式";
        } else if (message.contains("SQL")) {
            return "数据库操作失败，请联系管理员";
        } else if (message.contains("Excel")) {
            return "Excel文件格式错误，请检查文件内容";
        } else {
            return "处理失败: " + message;
        }
    }

    /**
     * 从行数据中提取明细信息
     *
     * @param rowData     行数据
     * @param psdInfo     配送单主表信息
     * @param rowIndex    行索引
     * @param purposeCode 用途编码
     * @return 明细对象
     */
    private PsdDetail extractDetailFromRow(Map<String, Object> rowData, PsdInfo psdInfo, int rowIndex, String purposeCode) {
        String goodsName = getStringValue(rowData, "C");
        String goodsCode = getStringValue(rowData, "B");
        String goodsUnit = getStringValue(rowData, "D");
        // 新增：E列（下单数量）
        String goodsOrderStr = getStringValue(rowData, "E");
        // G列（实际验收）
        String goodsCheckNumStr = getStringValue(rowData, "G");
        if (goodsCheckNumStr == null || goodsCheckNumStr.trim().isEmpty()) goodsCheckNumStr = "0";
        // 单价取H列，为空则为0
        String goodsPriceStr = getStringValue(rowData, "H");
        if (goodsPriceStr == null || goodsPriceStr.trim().isEmpty()) {
            goodsPriceStr = "0";
        }

        // 添加原始数据调试日志
        logger.debug("第{}行原始数据: 实际验收数量原始值='{}', 单价原始值='{}'",
            rowIndex + 1, goodsCheckNumStr, goodsPriceStr);
        if (!StringUtils.hasText(goodsName) || !StringUtils.hasText(goodsCode)) {
            logger.debug("第{}行缺少商品名称或编码，跳过", rowIndex + 1);
            return null;
        }
        PsdDetail detail = new PsdDetail();
        detail.setPsdInfoCode(psdInfo.getPsdHjkCode());
        detail.setCgdHjkCode(psdInfo.getPsdHjkCode());
        detail.setPsdDetailGoodsSupplier("安徽叶记生鲜食品配送有限公司");
        detail.setPsdDetailGoodsName(goodsName);
        detail.setPsdDetailGoodsCode(goodsCode);
        detail.setPsdDetailGoodsUnit(goodsUnit);
        // 解析E列：原始内容存describe，数字部分存num
        detail.setPsdDetailGoodsDescribe(goodsOrderStr);
        BigDecimal goodsNum = parseOrderNum(goodsOrderStr);
        detail.setPsdDetailGoodsNum(goodsNum);
        // G列实际验收
        BigDecimal goodsCheckNum = parseBigDecimal(goodsCheckNumStr);
        detail.setPsdDetailGoodsChecknum(goodsCheckNum);
        BigDecimal goodsPrice = parseBigDecimal(goodsPriceStr);
        detail.setPsdDetailGoodsPrice(goodsPrice);

        // 添加解析后数据调试日志
        logger.debug("第{}行解析后数据: 实际验收数量={}, 单价={}",
            rowIndex + 1, goodsCheckNum, goodsPrice);
        // 暂时不计算配送额，等价格更新后再计算
        // 先设置为0，后续会通过SQL更新
        detail.setPsdDetailGoodsAmount(BigDecimal.ZERO);

        // 添加调试日志
        logger.debug("第{}行明细数据: 商品={}, 编码={}, 下单数量={}, 实际验收={}, 临时配送额=0",
            rowIndex + 1, goodsName, goodsCode, goodsNum, goodsCheckNum);
        // 不再从Excel取税率
        detail.setPsdDetailGoodsSpecs(null);
        detail.setPsdDetailGoodType(null); // 保持为null，批量更新时由SQL赋值编码
        detail.setPsdDetailPurpose(purposeCode);
        detail.setPsdDetailWriDate(new Date());
        detail.setPsdDetailSyncStatus(0);
        logger.debug("第{}行明细数据: 商品={}, 编码={}, 下单数量={}, 实际验收={}, 单价={}, 金额={}", rowIndex + 1, goodsName, goodsCode, goodsNum, goodsCheckNum, goodsPrice, detail.getPsdDetailGoodsAmount());
        return detail;
    }

    /**
     * 解析BigDecimal值
     *
     * @param value 字符串值
     * @return BigDecimal对象
     */
    private BigDecimal parseBigDecimal(String value) {
        if (StringUtils.isEmpty(value)) {
            return null;
        }

        try {
            String cleanValue = value.trim();

            // 处理百分比格式（如：0.00%）
            if (cleanValue.endsWith("%")) {
                cleanValue = cleanValue.substring(0, cleanValue.length() - 1);
                BigDecimal percentage = new BigDecimal(cleanValue);
                // 转换为小数（除以100）
                return percentage.divide(new BigDecimal("100"), 4, RoundingMode.HALF_UP);
            }

            // 改进的数字解析逻辑
            // 1. 先尝试直接解析
            try {
                return new BigDecimal(cleanValue);
            } catch (NumberFormatException e1) {
                // 2. 如果失败，移除所有非数字字符（保留小数点和负号）
                String numericOnly = cleanValue.replaceAll("[^\\d.-]", "");
                if (StringUtils.isEmpty(numericOnly)) {
                    return null;
                }
                // 3. 确保只有一个负号且在最前面
                if (numericOnly.startsWith("-")) {
                    numericOnly = "-" + numericOnly.substring(1).replaceAll("-", "");
                } else {
                    numericOnly = numericOnly.replaceAll("-", "");
                }
                // 4. 确保只有一个小数点
                int dotIndex = numericOnly.indexOf('.');
                if (dotIndex != -1) {
                    String beforeDot = numericOnly.substring(0, dotIndex);
                    String afterDot = numericOnly.substring(dotIndex + 1);
                    numericOnly = beforeDot + "." + afterDot.replaceAll("\\.", "");
                }

                if (StringUtils.isEmpty(numericOnly)) {
                    return null;
                }
                return new BigDecimal(numericOnly);
            }
        } catch (NumberFormatException e) {
            logger.warn("解析数值失败: {}", value);
            return null;
        }
    }

    // 新增：解析下单数量字符串中的数字部分
    private BigDecimal parseOrderNum(String value) {
        if (StringUtils.isEmpty(value)) {
            return null;
        }
        try {
            // 提取第一个出现的数字（整数或小数）
            java.util.regex.Matcher m = java.util.regex.Pattern.compile("(\\d+\\.?\\d*)").matcher(value);
            if (m.find()) {
                return new BigDecimal(m.group(1));
            }
        } catch (Exception e) {
            logger.warn("解析下单数量失败: {}", value);
        }
        return null;
    }

    /**
     * 处理采购单Excel文件
     */
    @Transactional(rollbackFor = Exception.class)
    public ProcurementOrderProcessResult processProcurementOrderExcel(File file) {
        ProcurementOrderProcessResult result = new ProcurementOrderProcessResult();

        try {
            logger.info("开始处理采购单文件: {}", file.getName());

            // 读取Excel文件
            List<Map<String, Object>> excelData = readProcurementOrderExcel(file);
            result.setTotalCount(excelData.size());

            if (excelData.isEmpty()) {
                result.setErrorMessage("Excel文件中没有找到有效数据");
                return result;
            }

            // 从Excel数据中提取采购单主表信息
            CgdInfo cgdInfo = extractCgdInfoFromExcelData(excelData);

            if (cgdInfo != null) {
                // 检查是否已存在相同的采购单
                CgdInfo existingCgdInfo = cgdInfoMapper.selectByCgdCode(cgdInfo.getCgdCode());
                if (existingCgdInfo != null) {
                    logger.warn("采购单已存在，跳过处理: {}", cgdInfo.getCgdCode());
                    result.setErrorMessage("采购单已存在: " + cgdInfo.getCgdCode());
                    return result;
                }

                // 保存到数据库
                int insertResult = cgdInfoMapper.insert(cgdInfo);
                if (insertResult > 0) {
                    result.getProcessedData().add(cgdInfo);
                    result.setSuccessCount(1);
                    logger.info("采购单主表信息保存成功: {}", cgdInfo.getCgdCode());

                    // 处理采购单明细数据
                    List<CgdDetail> detailList = extractCgdDetailFromExcelData(excelData, cgdInfo);
                    if (!detailList.isEmpty()) {
                        // 设置明细数据的主表ID和采购单号
                        for (CgdDetail detail : detailList) {
                            detail.setCgdMainId(cgdInfo.getId());
                            detail.setCgdCode(cgdInfo.getCgdCode()); // 使用采购单主表的cgd_code
                        }

                        // 批量插入明细数据
                        int detailInsertCount = cgdDetailMapper.batchInsert(detailList);
                        logger.info("采购单明细数据插入成功，插入数量: {}", detailInsertCount);

                        // 更新明细的规格字段和商品类型字段
                        updateCgdDetailSpecs(cgdInfo.getId());

                        result.setProcessedDetailData(detailList);
                        result.setDetailCount(detailInsertCount);
                    } else {
                        logger.warn("采购单明细数据为空，跳过明细处理");
                        result.setDetailCount(0);
                    }
                } else {
                    result.setErrorCount(1);
                    result.setErrorMessage("保存采购单主表信息失败");
                    logger.error("保存采购单主表信息失败: {}", cgdInfo.getCgdCode());
                }
            } else {
                result.setErrorCount(1);
                result.setErrorMessage("无法从Excel数据中提取采购单信息");
                logger.error("无法从Excel数据中提取采购单信息");
            }

        } catch (Exception e) {
            logger.error("处理采购单文件时发生错误: {}", file.getName(), e);
            String errorMsg = getFriendlyErrorMessage(e);
            result.setErrorMessage(errorMsg);
            throw new RuntimeException("采购单处理失败: " + errorMsg, e);
        }

        return result;
    }

    /**
     * 读取采购单Excel文件
     */
    private List<Map<String, Object>> readProcurementOrderExcel(File file) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(file)) {
            Workbook workbook = WorkbookFactory.create(fis);
            logger.info("使用WorkbookFactory处理文件: {} (格式: {})", file.getName(), workbook.getClass().getSimpleName());

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();

            logger.info("采购单Excel文件总行数: {}", rowCount);

            // 读取所有行数据，包括明细数据
            for (int i = 0; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                Map<String, Object> rowData = new HashMap<>();
                int cellCount = row.getLastCellNum();

                for (int j = 0; j < cellCount; j++) {
                    Cell cell = row.getCell(j);
                    String columnLetter = getColumnLetter(j);
                    String cellValue = getCellValueWithMergedRegions(sheet, i, j);
                    rowData.put(columnLetter, cellValue);
                }

                data.add(rowData);
                logger.debug("读取第{}行数据: {}", i + 1, rowData);
            }

            workbook.close();
        }

        logger.info("采购单Excel文件读取完成，共读取{}行数据", data.size());
        return data;
    }

    /**
     * 从Excel数据中提取采购单主表信息
     */
    private CgdInfo extractCgdInfoFromExcelData(List<Map<String, Object>> excelData) {
        if (excelData.size() < 3) {
            logger.error("Excel数据行数不足，无法提取采购单信息，需要至少3行数据");
            return null;
        }

        logger.info("开始提取采购单信息，数据行数: {}", excelData.size());

        CgdInfo cgdInfo = new CgdInfo();

        try {
            // 获取第二行数据（索引为1）
            Map<String, Object> secondRow = excelData.get(1);
            // 获取第三行数据（索引为2）
            Map<String, Object> thirdRow = excelData.get(2);

            logger.info("第二行数据: {}", secondRow);
            logger.info("第三行数据: {}", thirdRow);

            // cgd_dept: 取第三行第二个单元格，截取中文括号之前的内容
            String deptCell = getStringValue(thirdRow, "B");
            if (deptCell != null && !deptCell.trim().isEmpty()) {
                String dept = extractDeptFromCell(deptCell);
                cgdInfo.setCgdDept(dept);
                logger.info("提取部门: {}", dept);
            } else {
                logger.warn("第三行第二个单元格为空，无法提取部门信息");
                cgdInfo.setCgdDept("");
            }

            // cgd_code: 取第二行第二个单元格内容
            String codeCell = getStringValue(secondRow, "B");
            if (codeCell != null && !codeCell.trim().isEmpty()) {
                cgdInfo.setCgdCode(codeCell.trim());
                logger.info("提取采购单号: {}", codeCell.trim());
            } else {
                logger.warn("第二行第二个单元格为空，无法提取采购单号");
                cgdInfo.setCgdCode("");
            }

            // cgd_info_dis_date: 取第二行第六个单元格内容
            String disDateCell = getStringValue(secondRow, "F");
            if (disDateCell != null && !disDateCell.trim().isEmpty()) {
                Date disDate = parseDate(disDateCell.trim());
                cgdInfo.setCgdInfoDisDate(disDate);
                logger.info("提取配送时间: {}", disDate);
            } else {
                logger.warn("第二行第六个单元格为空，无法提取配送时间");
                cgdInfo.setCgdInfoDisDate(null);
            }

            // cgd_info_date: 取第二行第四个单元格内容
            String dateCell = getStringValue(secondRow, "D");
            if (dateCell != null && !dateCell.trim().isEmpty()) {
                Date date = parseDate(dateCell.trim());
                cgdInfo.setCgdInfoDate(date);
                logger.info("提取开单时间: {}", date);
            } else {
                logger.warn("第二行第四个单元格为空，无法提取开单时间");
                cgdInfo.setCgdInfoDate(null);
            }

            // cgd_info_supplier: 固定值
            cgdInfo.setCgdInfoSupplier("安徽叶记生鲜食品配送有限公司");
            logger.info("设置供应商: {}", cgdInfo.getCgdInfoSupplier());

            // 设置其他字段的默认值
            cgdInfo.setCgdInfoWriDate(new Date());
            cgdInfo.setCgdInfoStatus(0);
            cgdInfo.setCgdInfoSyncStatus(0);

            logger.info("采购单信息提取完成: {}", cgdInfo);
            return cgdInfo;

        } catch (Exception e) {
            logger.error("提取采购单信息时发生错误", e);
            return null;
        }
    }

    /**
     * 从单元格中提取部门名称（截取中文括号之前的内容）
     * 格式：中国联合网络通信有限公司安徽省分公司（风味）
     * 提取：中国联合网络通信有限公司安徽省分公司
     */
    private String extractDeptFromCell(String cellValue) {
        if (cellValue == null) {
            return "";
        }

        String dept = cellValue.trim();

        // 查找中文括号的位置
        int endIndex = dept.indexOf("（");
        if (endIndex == -1) {
            endIndex = dept.indexOf("(");
        }

        if (endIndex != -1) {
            dept = dept.substring(0, endIndex);
        }

        return dept.trim();
    }

    /**
     * 解析日期字符串
     * 支持格式：2025-07-07、2025-07-06 09:06:30、yyyy/M/d等
     */
    private Date parseDate(String dateStr) {
        if (dateStr == null || dateStr.trim().isEmpty()) {
            return null;
        }

        String trimmedDate = dateStr.trim();

        // 尝试多种日期格式
        String[] dateFormats = {
                "yyyy-MM-dd HH:mm:ss",  // 2025-07-06 09:06:30
                "yyyy-MM-dd",           // 2025-07-07
                "yyyy/M/d",             // 2025/7/4
                "yyyy/MM/dd",           // 2025/07/04
                "yyyy-MM-dd HH:mm",     // 2025-07-06 09:06
                "yyyy/M/d HH:mm:ss",    // 2025/7/4 09:06:30
                "yyyy/M/d HH:mm"        // 2025/7/4 09:06
        };

        for (String format : dateFormats) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                sdf.setLenient(false); // 严格模式
                Date date = sdf.parse(trimmedDate);
                logger.debug("成功解析日期: {} -> {} (格式: {})", trimmedDate, date, format);
                return date;
            } catch (ParseException e) {
                // 继续尝试下一个格式
                continue;
            }
        }

        logger.error("无法解析日期: {}，尝试了所有支持的格式", trimmedDate);
        return null;
    }

    /**
     * 从Excel数据中提取采购单明细信息
     */
    private List<CgdDetail> extractCgdDetailFromExcelData(List<Map<String, Object>> excelData, CgdInfo cgdInfo) {
        List<CgdDetail> detailList = new ArrayList<>();

        if (excelData.size() < 7) {
            logger.warn("Excel数据行数不足，无法提取明细数据，需要至少7行数据");
            return detailList;
        }

        // 获取第三行第二个单元格的客户名称，用于提取用途
        String customerName = null;
        if (excelData.size() > 2) {
            customerName = getStringValue(excelData.get(2), "B"); // 第三行第二列
            logger.debug("从第三行第二列获取客户名称: {}", customerName);
        }

        // 提取用途编码
        String purposeCode = null;
        if (customerName != null) {
            String purposeName = extractPurposeFromCustomerName(customerName);
            if (purposeName != null) {
                purposeCode = getPurposeCodeByName(purposeName);
                logger.info("提取用途编码: 客户名称={}, 用途名称={}, 用途编码={}", customerName, purposeName, purposeCode);
            }
        }

        // 从第七行开始读取明细数据（第七行是表头）
        for (int i = 7; i < excelData.size(); i++) {
            Map<String, Object> rowData = excelData.get(i);

            // 检查是否有有效数据
            boolean hasData = false;
            for (Object value : rowData.values()) {
                if (value != null && !value.toString().trim().isEmpty()) {
                    hasData = true;
                    break;
                }
            }

            if (!hasData) {
                continue; // 跳过空行
            }

            // 检查第一列是否为"合计"，如果是则停止读取明细数据
            String firstCellValue = getStringValue(rowData, "A");
            if (firstCellValue != null && firstCellValue.trim().equals("合计")) {
                logger.info("遇到合计行，明细数据读取结束，行号: {}", i + 1);
                break; // 停止读取明细数据
            }

            CgdDetail detail = new CgdDetail();

            // 设置基本信息
            detail.setCgdInfoDate(new Date());
            detail.setCgdDetailSyncStatus(0);

            // 设置关联字段
            detail.setCgdMainId(cgdInfo.getId());
            detail.setCgdCode(cgdInfo.getCgdCode());

            // 根据字段映射规则提取数据
            // cgd_detail_goods_supplier 取固定值
            detail.setCgdDetailGoodsSupplier("安徽叶记生鲜食品配送有限公司");

            // cgd_detail_goods_name 取商品名称列（第3列，C列）
            String goodsName = getStringValue(rowData, "C");
            detail.setCgdDetailGoodsName(goodsName != null ? goodsName.trim() : "");

            // cgd_detail_goods_code 取商品编码列（第2列，B列）
            String goodsCode = getStringValue(rowData, "B");
            detail.setCgdDetailGoodsCode(goodsCode != null ? goodsCode.trim() : "");

            // cgd_detail_goods_unit 取下单单位列（第7列，G列）
            String goodsUnit = getStringValue(rowData, "G");
            detail.setCgdDetailGoodsUnit(goodsUnit);

            // cgd_detail_goods_num 取下单数量（第9列，I列）
            String goodsNum = getStringValue(rowData, "I");
            if (goodsNum != null && !goodsNum.trim().isEmpty()) {
                try {
                    detail.setCgdDetailGoodsNum(new BigDecimal(goodsNum.trim()));
                } catch (NumberFormatException e) {
                    logger.warn("下单数量格式错误: {}", goodsNum);
                    detail.setCgdDetailGoodsNum(BigDecimal.ZERO);
                }
            } else {
                detail.setCgdDetailGoodsNum(BigDecimal.ZERO);
            }

            // cgd_detail_goods_price 取下单单价(元)（第8列，H列）
            String goodsPrice = getStringValue(rowData, "H");
            if (goodsPrice != null && !goodsPrice.trim().isEmpty()) {
                try {
                    BigDecimal price = new BigDecimal(goodsPrice.trim());
                    // 保留两位小数
                    price = price.setScale(2, RoundingMode.HALF_UP);
                    detail.setCgdDetailGoodsPrice(price);
                } catch (NumberFormatException e) {
                    logger.warn("下单单价格式错误: {}", goodsPrice);
                    detail.setCgdDetailGoodsPrice(BigDecimal.ZERO);
                }
            } else {
                detail.setCgdDetailGoodsPrice(BigDecimal.ZERO);
            }

            // cgd_detail_purpose 设置用途编码
            detail.setCgdDetailPurpose(purposeCode);

            // 设置其他字段的默认值
            detail.setCgdDetailGoodsSpecs(""); // 需要通过商品编码关联goods_info表获取
            detail.setCgdDetailRemark(getStringValue(rowData, "Q")); // 备注（第17列，Q列）

            // 设置其他字段为null或默认值
            detail.setCgdDetailGoodsType(null);
            detail.setCgdDetailReserve1(null);
            detail.setCgdDetailReserve2(null);
            detail.setCgdDetailReserve3(null);
            detail.setOutcgdInfoId(null);
            detail.setCgdDetailCode(null);
            detail.setCgdDetailIsdelete("0");
            detail.setCgdDetailIsdeletetime(null);
            detail.setCgdDetailGoodsCheckstatus(null);
            detail.setCgdDetailGoodsCheckstatusremark(null);
            detail.setCgdDetailSyncMsg(null);
            detail.setCgdDetailSyncDate(null);

            detailList.add(detail);
            logger.debug("提取明细数据: 商品名称={}, 商品编码={}, 数量={}, 单价={}, 用途编码={}",
                    detail.getCgdDetailGoodsName(), detail.getCgdDetailGoodsCode(),
                    detail.getCgdDetailGoodsNum(), detail.getCgdDetailGoodsPrice(), detail.getCgdDetailPurpose());
        }

        logger.info("从Excel中提取到 {} 条明细数据", detailList.size());
        return detailList;
    }

    /**
     * 更新采购单明细的规格字段和商品类型字段
     *
     * @param cgdInfoId 采购单主表ID
     */
    private void updateCgdDetailSpecs(Long cgdInfoId) {
        try {
            logger.info("开始更新采购单明细规格字段和商品类型字段，采购单ID: {}", cgdInfoId);

            // 使用一条SQL语句批量更新所有明细的规格字段和商品类型字段
            int updateCount = cgdDetailMapper.batchUpdateSpecsAndTypeByCgdInfoId(cgdInfoId);
            logger.info("采购单ID: {} 的明细规格字段和商品类型字段更新完成，影响行数: {}", cgdInfoId, updateCount);

        } catch (Exception e) {
            logger.error("更新采购单明细规格字段和商品类型字段失败，采购单ID: {}", cgdInfoId, e);
            throw new RuntimeException("更新采购单明细规格字段和商品类型字段失败", e);
        }
    }

    /**
     * 根据用途名称获取用途编码
     *
     * @param purposeName 用途名称
     * @return 用途编码，如果未找到则返回null
     */
    private String getPurposeCodeByName(String purposeName) {
        if (purposeName == null || purposeName.trim().isEmpty()) {
            return null;
        }

        String code = PURPOSE_NAME_TO_CODE_MAP.get(purposeName.trim());
        if (code != null) {
            logger.debug("找到用途编码: {} -> {}", purposeName, code);
        } else {
            logger.warn("未找到用途名称对应的编码: {}", purposeName);
        }
        return code;
    }

    /**
     * 校验配送单明细中的商品编码是否都存在于商品信息表中
     * @param detailList 配送单明细列表
     * @throws RuntimeException 如果存在无效商品编码则抛出异常
     */
    private void validatePsdDetailGoodsCodes(List<PsdDetail> detailList) {
        // 收集所有唯一的商品编码
        Set<String> goodsCodes = detailList.stream()
                .map(PsdDetail::getPsdDetailGoodsCode)
                .filter(code -> code != null && !code.trim().isEmpty())
                .collect(Collectors.toSet());
        
        if (goodsCodes.isEmpty()) {
            return; // 没有商品编码需要校验
        }
        
        // 查询数据库中存在的商品编码
        List<String> existingCodes = goodsInfoMapper.checkExistingGoodsCodes(new ArrayList<>(goodsCodes));
        
        // 找出不存在的商品编码
        Set<String> nonExistingCodes = new HashSet<>(goodsCodes);
        nonExistingCodes.removeAll(existingCodes);
        
        if (!nonExistingCodes.isEmpty()) {
            String errorMsg = "以下商品编码在商品信息表中不存在: " + String.join(", ", nonExistingCodes);
            logger.error(errorMsg);
            throw new RuntimeException(errorMsg);
        }
    }
    
    /**
     * 格式化发货量显示
     * 如果为空则显示0，如果带有小数但小数位为0，则只显示整数
     *
     * @param goodsNum 发货量
     * @return 格式化后的字符串
     */
    private String formatGoodsNum(BigDecimal goodsNum) {
        if (goodsNum == null) {
            return "0";
        }

        // 去除末尾的0
        BigDecimal stripped = goodsNum.stripTrailingZeros();

        // 如果是整数（scale <= 0），则显示为整数
        if (stripped.scale() <= 0) {
            return stripped.toPlainString();
        }

        // 否则显示完整的小数
        return stripped.toPlainString();
    }

    /**
     * 导出本次导入成功的配送单到指定文件夹，每个配送单一个Excel，按新模板格式
     */
    public void exportDeliveryOrdersToExcel(File folder, List<String> psdHjkCodeList) throws Exception {
        if (psdHjkCodeList == null || psdHjkCodeList.isEmpty()) {
            logger.warn("psdHjkCodeList为空，未导出任何文件");
            return;
        }
        logger.info("开始导出配送单，目标文件夹: {}，单号列表: {}", folder.getAbsolutePath(), psdHjkCodeList);
        for (String psdHjkCode : psdHjkCodeList) {
            logger.info("准备导出配送单: {}", psdHjkCode);
            PsdInfo psdInfo = psdInfoMapper.selectByHjkCode(psdHjkCode);
            if (psdInfo == null) {
                logger.warn("未查到主表数据，跳过导出: {}", psdHjkCode);
                continue;
            }
            List<PsdDetail> detailList = psdDetailMapper.selectByPsdInfoCode(psdHjkCode);
            if (detailList == null || detailList.isEmpty()) {
                logger.warn("未查到明细数据，跳过导出: {}", psdHjkCode);
                continue;
            }
            // 按psd_detail_purpose分组
            Map<String, List<PsdDetail>> purposeGroup = new HashMap<>();
            for (PsdDetail detail : detailList) {
                String purpose = detail.getPsdDetailPurpose();
                if (purpose == null) purpose = "未分组";
                purposeGroup.computeIfAbsent(purpose, k -> new ArrayList<>()).add(detail);
            }
            for (Map.Entry<String, List<PsdDetail>> entry : purposeGroup.entrySet()) {
                String purpose = entry.getKey();
                List<PsdDetail> groupDetails = entry.getValue();
                // 新文件名格式：配送日期-配送部门-用途.xlsx
                String dateStr = psdInfo.getPsdInfoDate() != null ?
                    new java.text.SimpleDateFormat("yyyyMMdd").format(psdInfo.getPsdInfoDate()) : "";
                String deptName = psdInfo.getPsdDept();
                // 可根据部门编码映射中文名
                if ("T002".equals(deptName)) deptName = "联通省分公司";
                else if ("T003".equals(deptName)) deptName = "联通合肥分公司";
                String purposeName = PURPOSE_CODE_TO_NAME_MAP.getOrDefault(purpose, purpose);
                String fileName = dateStr + "-" + deptName + "-" + purposeName + ".xlsx";
                File outFile = new File(folder, fileName);
                if (outFile.exists()) {
                    boolean deleted = outFile.delete();
                    logger.info("导出文件已存在，已覆盖: {}，删除结果:{}", outFile.getAbsolutePath(), deleted);
                }
                logger.info("导出文件路径: {}", outFile.getAbsolutePath());
                try {
                    exportSingleDeliveryOrderExcel(psdInfo, groupDetails, outFile);
                    logger.info("导出成功: {}", outFile.getAbsolutePath());
                } catch (Exception e) {
                    logger.error("导出失败: {}，异常: {}", outFile.getAbsolutePath(), e.getMessage(), e);
                }
            }
        }
    }

    /**
     * 按新模板导出单个配送单Excel
     */
    private void exportSingleDeliveryOrderExcel(PsdInfo psdInfo, List<PsdDetail> detailList, File outFile) throws Exception {
        org.apache.poi.xssf.usermodel.XSSFWorkbook wb = new org.apache.poi.xssf.usermodel.XSSFWorkbook();
        org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet("配送单");
        int rowIdx = 0;
        int colCount = 9; // 增加配送额列，共9列

        // 样式定义
        org.apache.poi.ss.usermodel.CellStyle borderStyle = wb.createCellStyle();
        borderStyle.setBorderTop(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        borderStyle.setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        borderStyle.setBorderLeft(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        borderStyle.setBorderRight(org.apache.poi.ss.usermodel.BorderStyle.THIN);

        // 统一字体设置
        org.apache.poi.ss.usermodel.Font normalFont = wb.createFont();
        normalFont.setFontHeightInPoints((short) 11);

        org.apache.poi.ss.usermodel.Font boldFont = wb.createFont();
        boldFont.setBold(true);
        boldFont.setFontHeightInPoints((short) 11);

        org.apache.poi.ss.usermodel.Font bigBoldFont = wb.createFont();
        bigBoldFont.setBold(true);
        bigBoldFont.setFontHeightInPoints((short) 16);

        org.apache.poi.ss.usermodel.CellStyle bigBoldCenterStyle = wb.createCellStyle();
        bigBoldCenterStyle.cloneStyleFrom(borderStyle);
        bigBoldCenterStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        bigBoldCenterStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        bigBoldCenterStyle.setFont(bigBoldFont);

        org.apache.poi.ss.usermodel.CellStyle boldCenterStyle = wb.createCellStyle();
        boldCenterStyle.cloneStyleFrom(borderStyle);
        boldCenterStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        boldCenterStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        boldCenterStyle.setFont(boldFont);

        org.apache.poi.ss.usermodel.CellStyle normalStyle = wb.createCellStyle();
        normalStyle.cloneStyleFrom(borderStyle);
        normalStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        normalStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        normalStyle.setFont(normalFont);

        org.apache.poi.ss.usermodel.CellStyle noBorderStyle = wb.createCellStyle();
        noBorderStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.LEFT);
        noBorderStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        noBorderStyle.setFont(normalFont);

        // 第一行：公司名
        org.apache.poi.ss.usermodel.Row row0 = sheet.createRow(rowIdx++);
        org.apache.poi.ss.usermodel.Cell cell0 = row0.createCell(0);
        cell0.setCellValue("安徽叶记生鲜食品配送有限公司");
        cell0.setCellStyle(bigBoldCenterStyle);
        for (int i = 1; i < colCount; i++) row0.createCell(i).setCellStyle(bigBoldCenterStyle);
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, colCount - 1));
        row0.setHeightInPoints(28);

        // 第二行：配送清单
        org.apache.poi.ss.usermodel.Row row1 = sheet.createRow(rowIdx++);
        org.apache.poi.ss.usermodel.Cell cell1 = row1.createCell(0);
        cell1.setCellValue("配送清单");
        cell1.setCellStyle(bigBoldCenterStyle);
        for (int i = 1; i < colCount; i++) row1.createCell(i).setCellStyle(bigBoldCenterStyle);
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(1, 1, 0, colCount - 1));
        row1.setHeightInPoints(24);

        // 新增：客户行（插入在表头前，样式与原配送单号/到货日期行一致，左对齐+有边框）
        org.apache.poi.ss.usermodel.CellStyle leftBorderStyle = wb.createCellStyle();
        leftBorderStyle.cloneStyleFrom(borderStyle);
        leftBorderStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.LEFT);
        leftBorderStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        leftBorderStyle.setFont(normalFont);
        org.apache.poi.ss.usermodel.Row customerRow = sheet.createRow(rowIdx++);
        org.apache.poi.ss.usermodel.Cell customerCell = customerRow.createCell(0);
        // 获取部门中文名
        String deptName = psdInfo.getPsdDept();
        if ("T002".equals(deptName)) deptName = "联通省分公司";
        else if ("T003".equals(deptName)) deptName = "联通合肥分公司";
        // 获取用途名称
        String purposeName = null;
        if (detailList != null && !detailList.isEmpty()) {
            String purposeCode = detailList.get(0).getPsdDetailPurpose();
            if (purposeCode != null) {
                purposeName = PURPOSE_CODE_TO_NAME_MAP.getOrDefault(purposeCode, purposeCode);
            }
        }
        if (purposeName == null) purposeName = "";
        customerCell.setCellValue("客户：" + deptName + (purposeName.isEmpty() ? "" : ("-" + purposeName)));
        customerCell.setCellStyle(leftBorderStyle);
        for (int i = 1; i < colCount; i++) customerRow.createCell(i).setCellStyle(leftBorderStyle);
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(2, 2, 0, colCount - 1));
//        customerRow.setHeightInPoints(24);

        // 第三行：配送单号、到货日期（合并为一个单元格）
        org.apache.poi.ss.usermodel.Row row2 = sheet.createRow(rowIdx++);
        String deliveryInfo = "配送单号：" + psdInfo.getPsdHjkCode() + "   到货日期：" +
                (psdInfo.getPsdInfoDate() != null ?
                        new java.text.SimpleDateFormat("yyyy-MM-dd").format(psdInfo.getPsdInfoDate()) : "");
        org.apache.poi.ss.usermodel.Cell mergedCell = row2.createCell(0);
        mergedCell.setCellValue(deliveryInfo);
        mergedCell.setCellStyle(leftBorderStyle); // 保持与客户行一致
        for (int i = 1; i < colCount; i++) {
            row2.createCell(i).setCellStyle(leftBorderStyle);
        }
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(3, 3, 0, colCount - 1));

        // 明细标题
        org.apache.poi.ss.usermodel.Row row3 = sheet.createRow(rowIdx++);
        String[] titles = {"编码", "商品名", "规格", "单位", "税率", "发货量", "配送价", "配送额", "备注"};
        for (int i = 0; i < titles.length; i++) {
            org.apache.poi.ss.usermodel.Cell c = row3.createCell(i);
            c.setCellValue(titles[i]);
            c.setCellStyle(boldCenterStyle); // 保证所有格子有边框
        }

        // 明细数据
        int detailCount = 0;
        double numTotal = 0;
        BigDecimal amountTotal = BigDecimal.ZERO;
        for (PsdDetail detail : detailList) {
            org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIdx++);
            row.createCell(0).setCellValue(detail.getPsdDetailGoodsCode());
            row.createCell(1).setCellValue(detail.getPsdDetailGoodsName());
            row.createCell(2).setCellValue(detail.getPsdDetailGoodsSpecs());
            row.createCell(3).setCellValue(detail.getPsdDetailGoodsUnit());
            String rateStr = detail.getPsdDetailGoodsRate() != null ?
                detail.getPsdDetailGoodsRate().multiply(new java.math.BigDecimal("100")).setScale(0, java.math.RoundingMode.HALF_UP).toPlainString() + "%" : "0%";
            row.createCell(4).setCellValue(rateStr);
            // 发货量取psd_detail_goods_checknum字段，如果为0或空则显示空
            BigDecimal checkNum = detail.getPsdDetailGoodsChecknum();
            if (checkNum != null && checkNum.compareTo(BigDecimal.ZERO) != 0) {
                String goodsNumStr = formatGoodsNum(checkNum);
                row.createCell(5).setCellValue(goodsNumStr);
            } else {
                row.createCell(5).setCellValue("");
            }
            double price = detail.getPsdDetailGoodsPrice() != null ? detail.getPsdDetailGoodsPrice().doubleValue() : 0;
            row.createCell(6).setCellValue(price);
            double num = checkNum != null ? checkNum.doubleValue() : 0;
            // 配送额列
            BigDecimal amount = detail.getPsdDetailGoodsAmount();
            if (amount != null && amount.compareTo(BigDecimal.ZERO) != 0) {
                row.createCell(7).setCellValue(amount.doubleValue());
            } else {
                row.createCell(7).setCellValue("");
            }
            // 备注列
            row.createCell(8).setCellValue(detail.getPsdDetailGoodsRemark() != null ? detail.getPsdDetailGoodsRemark() : "");
            for (int i = 0; i < colCount; i++) row.getCell(i).setCellStyle(normalStyle);
            detailCount++;
            numTotal += num; // 统计数量总计（使用实际验收数量）
            // 含税价格总计：使用psd_detail_goods_amount字段之和
            if (amount != null) {
                amountTotal = amountTotal.add(amount);
            }
        }

        // 明细统计行（合并为一个单元格，无边框）
        org.apache.poi.ss.usermodel.Row statRow = sheet.createRow(rowIdx++);
        String amountTotalStr = "";
        if (amountTotal.compareTo(BigDecimal.ZERO) != 0) {
            amountTotalStr = String.format("%.2f", amountTotal);
        }
        String stats = "本单品种数：" + detailCount + "         数量总计：" + numTotal + "         含税价格总计：" + amountTotalStr;
        org.apache.poi.ss.usermodel.Cell statsCell = statRow.createCell(0);
        statsCell.setCellValue(stats);
        statsCell.setCellStyle(noBorderStyle);
        for (int i = 1; i < colCount; i++) {
            statRow.createCell(i).setCellStyle(noBorderStyle);
        }
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(rowIdx - 1, rowIdx - 1, 0, colCount - 1));
        statRow.setHeightInPoints(36);

        // 配送人信息行（从配送单主表获取）
        org.apache.poi.ss.usermodel.Row delivererRow = sheet.createRow(rowIdx++);
        String delivererInfo = "配送员：" + (psdInfo.getPsdInfoPsyName() != null ? psdInfo.getPsdInfoPsyName() : "") +
                "         配送员电话：" + (psdInfo.getPsdInfoPsyPhone() != null ? psdInfo.getPsdInfoPsyPhone() : "") +
                "         配送员签字：";
        org.apache.poi.ss.usermodel.Cell delivererCell = delivererRow.createCell(0);
        delivererCell.setCellValue(delivererInfo);
        delivererCell.setCellStyle(noBorderStyle);
        for (int i = 1; i < colCount; i++) {
            delivererRow.createCell(i).setCellStyle(noBorderStyle);
        }
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(rowIdx - 1, rowIdx - 1, 0, colCount - 1));
        delivererRow.setHeightInPoints(36);

        // 收货人信息行（合并为一个单元格，无边框）
        org.apache.poi.ss.usermodel.Row receiverRow = sheet.createRow(rowIdx++);
        String receiverInfo = "收货人：" + (psdInfo.getPsdInfoReceiverName() != null ? psdInfo.getPsdInfoReceiverName() : "") +
                "         收货人电话：" + (psdInfo.getPsdInfoReceiverPhone() != null ? psdInfo.getPsdInfoReceiverPhone() : "") +
                "         收货人地址：" + (psdInfo.getPsdInfoReceiverAddress() != null ? psdInfo.getPsdInfoReceiverAddress() : "") +
                "\n收货人签字：";
        org.apache.poi.ss.usermodel.CellStyle wrapStyle = wb.createCellStyle();
        wrapStyle.cloneStyleFrom(noBorderStyle);
        wrapStyle.setWrapText(true); // 允许换行
        org.apache.poi.ss.usermodel.Cell receiverCell = receiverRow.createCell(0);
        receiverCell.setCellValue(receiverInfo);
        receiverCell.setCellStyle(wrapStyle); // 使用支持自动换行的样式
        for (int i = 1; i < colCount; i++) {
            receiverRow.createCell(i).setCellStyle(noBorderStyle);
        }
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(rowIdx - 1, rowIdx - 1, 0, colCount - 1));
        receiverRow.setHeightInPoints(48); // 可根据内容调整高度

        // 设置列宽 - 增加配送额列宽度
        int[] colWidths = {20, 20, 20, 12, 10, 20, 12, 12, 16}; // 增加配送额列
        for (int i = 0; i < colCount; i++) {
            sheet.setColumnWidth(i, colWidths[i] * 256);
        }

        // 写出文件
        try (java.io.FileOutputStream fos = new java.io.FileOutputStream(outFile)) {
            wb.write(fos);
            logger.info("文件写入完成: {}", outFile.getAbsolutePath());
        } catch (Exception e) {
            logger.error("写入文件异常: {}，异常: {}", outFile.getAbsolutePath(), e.getMessage(), e);
            throw e;
        } finally {
            wb.close();
        }
    }


    // 明细按用途覆盖或插入
    private void upsertPsdDetails(PsdInfo psdInfo, List<PsdDetail> details) {
        if (psdInfo == null || details == null || details.isEmpty()) return;
        // 按用途分组
        Map<String, List<PsdDetail>> purposeMap = new HashMap<>();
        for (PsdDetail d : details) {
            String purpose = d.getPsdDetailPurpose();
            if (purpose == null) purpose = "";
            purposeMap.computeIfAbsent(purpose, k -> new ArrayList<>()).add(d);
        }
        for (Map.Entry<String, List<PsdDetail>> entry : purposeMap.entrySet()) {
            String purpose = entry.getKey();
            // 先删除该主表下此用途的明细
            psdDetailMapper.deleteByPsdInfoIdAndPurpose(psdInfo.getId(), purpose);
            // 插入新明细
            for (PsdDetail d : entry.getValue()) {
                d.setPsdInfoCode(psdInfo.getPsdHjkCode());
                d.setOutpsdInfoId(psdInfo.getId());
                psdDetailMapper.insert(d);
            }
        }
        // 4.1 插入后批量更新明细商品信息和品类，同时计算配送额
        int updateCount = psdDetailMapper.batchUpdateSpecsAndTypeByPsdInfoCode(psdInfo.getPsdHjkCode());
        logger.info("配送单 {} 的明细商品信息和配送额更新完成，影响行数: {}", psdInfo.getPsdHjkCode(), updateCount);

        // 4.2 验证配送额计算是否正确
        List<PsdDetail> updatedDetails = psdDetailMapper.selectByPsdInfoCode(psdInfo.getPsdHjkCode());
        for (PsdDetail detail : updatedDetails) {
            if (detail.getPsdDetailGoodsChecknum() != null && detail.getPsdDetailGoodsPrice() != null) {
                BigDecimal expectedAmount = detail.getPsdDetailGoodsChecknum().multiply(detail.getPsdDetailGoodsPrice()).setScale(2, RoundingMode.HALF_UP);
                if (detail.getPsdDetailGoodsAmount() != null && detail.getPsdDetailGoodsAmount().compareTo(expectedAmount) != 0) {
                    logger.warn("配送额计算不一致: 商品={}, 实际验收={}, 单价={}, 计算配送额={}, 数据库配送额={}",
                        detail.getPsdDetailGoodsName(), detail.getPsdDetailGoodsChecknum(),
                        detail.getPsdDetailGoodsPrice(), expectedAmount, detail.getPsdDetailGoodsAmount());
                } else {
                    logger.debug("配送额计算正确: 商品={}, 实际验收={}, 单价={}, 配送额={}",
                        detail.getPsdDetailGoodsName(), detail.getPsdDetailGoodsChecknum(),
                        detail.getPsdDetailGoodsPrice(), detail.getPsdDetailGoodsAmount());
                }
            }
        }
    }

    /**
     * 按部门和配送时间查询所有主表单号
     */
    public List<String> getPsdHjkCodesByDeptAndDate(String psdDept, java.util.Date psdInfoDate) {
        List<PsdInfo> list = psdInfoMapper.selectListByDeptAndDate(psdDept, psdInfoDate);
        List<String> codes = new ArrayList<>();
        if (list != null) {
            for (PsdInfo info : list) {
                if (info.getPsdHjkCode() != null) {
                    codes.add(info.getPsdHjkCode());
                }
            }
        }
        return codes;
    }

    /**
     * 新重载：处理配送单Excel文件，支持客户部门参数，每个文件独立处理
     */
    public List<DeliveryOrderProcessResult> processDeliveryOrderExcel(List<File> files, String psdDept) {
        List<DeliveryOrderProcessResult> resultList = new ArrayList<>();
        if (files == null || files.isEmpty()) {
            DeliveryOrderProcessResult result = new DeliveryOrderProcessResult();
            result.setErrorMessage("未选择任何配送单文件");
            resultList.add(result);
            return resultList;
        }
        for (File file : files) {
            DeliveryOrderProcessResult result = new DeliveryOrderProcessResult();
            try {
                // 1. 提取主表
                List<Map<String, Object>> excelData = readDeliveryOrderExcel(file);
                PsdInfoWithPurpose infoWithPurpose = extractPsdInfoWithPurposeFromExcelData(excelData, psdDept);
                PsdInfo psdInfo = infoWithPurpose.psdInfo;
                psdInfo.setPsdDept(psdDept);

                // 2. 查重/新建主表
                PsdInfo exist = psdInfoMapper.selectByDeptAndDate(psdInfo.getPsdDept(), psdInfo.getPsdInfoDate());
                if (exist == null) {
                    psdInfoMapper.insert(psdInfo);
                } else {
                    psdInfo = exist;
                }

                // 3. 提取明细
                List<PsdDetail> details = new ArrayList<>();
                if (excelData.size() > 3) {
                    for (int i = 2; i < excelData.size(); i++) {
                        Map<String, Object> row = excelData.get(i);
                        String firstCell = getStringValue(row, "A");
                        if (firstCell != null && (firstCell.contains("第") || firstCell.contains("页"))) {
                            logger.info("遇到分页行，明细数据读取结束，行号: {}", i + 1);
                            break;
                        }
                        String purposeCode = row.containsKey("用途") ? row.get("用途").toString() : infoWithPurpose.purposeCode;
                        PsdDetail detail = extractDetailFromRow(row, psdInfo, i, purposeCode);
                        if (detail != null) {
                            details.add(detail);
                        }
                    }
                }

                // 4. 校验配送单明细中的商品编码是否都存在于商品信息表中
                validatePsdDetailGoodsCodes(details);
                
                // 5. 按用途覆盖或插入明细
                upsertPsdDetails(psdInfo, details);

                // 5. 记录结果
                result.setProcessedData(Collections.singletonList(psdInfo));
                result.setProcessedDetailData(details);
                result.setSuccessCount(1);
                result.setDetailCount(details.size());
            } catch (Exception e) {
                result.setErrorCount(1);
                result.setErrorMessage(e.getMessage());
                logger.error("处理配送单文件时发生错误: {}", file.getName(), e);
            }
            resultList.add(result);
        }
        return resultList;
    }

}